﻿Imports System.Data.SqlClient

Public Class FrmTraHoSoBienDong
    Private Sub NapLoaiDat1()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select kyhieu, tenld from LOAIDAT", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLD.DisplayMember = "kyhieu"
        CmbLD.ValueMember = "kyhieu"
        CmbLD.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapHinhThuc()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMHinhThuc", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbHinhThuc.DisplayMember = "TenHinhThuc"
        CmbHinhThuc.ValueMember = "maHinhThucID"
        CmbHinhThuc.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbHuyen.DisplayMember = "T_huyen"
        CmbHuyen.ValueMember = "maH"
        CmbHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiTaiLieu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTaiLieu", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLoaiTaiLieu.DisplayMember = "TenLoaiTaiLieu"
        CmbLoaiTaiLieu.ValueMember = "maLoaiTaiLieuID"
        CmbLoaiTaiLieu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiBienDong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from LOAIBD", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cbLoaiBienDong.DisplayMember = "TenLoaiBD"
        cbLoaiBienDong.ValueMember = "maBD"
        cbLoaiBienDong.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapPhong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMPhong", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cmbPhong.DisplayMember = "Tenphong"
        cmbPhong.ValueMember = "maPhongID"
        cmbPhong.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapNgan()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMNgan", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbNgan.DisplayMember = "TenNgan"
        CmbNgan.ValueMember = "maNganID"
        CmbNgan.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKe()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKe", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbKe.DisplayMember = "TenKe"
        CmbKe.ValueMember = "maKeID"
        CmbKe.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKho()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKho", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbKho.DisplayMember = "TenKho"
        CmbKho.ValueMember = "maKhoID"
        CmbKho.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapTinhTrang()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTinhTrang", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbTinhTrang.DisplayMember = "TenTinhTrang"
        CmbTinhTrang.ValueMember = "maTinhTrangID"
        CmbTinhTrang.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub CmbHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CmbHuyen.SelectedIndexChanged
        ' load ten xa
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & CmbHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbXa.DisplayMember = "TenXa"
        CmbXa.ValueMember = "maxa"
        CmbXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapVaoTexBox()
        TextSoHoSo.DataBindings.Clear()
        TextSoHoSo.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "SoHoSo")
        TextTenChu.DataBindings.Clear()
        TextTenChu.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "tenchu")
        TextDiaChi.DataBindings.Clear()
        TextDiaChi.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "diachi")
        CmbTinhTrang.DataBindings.Clear()
        CmbTinhTrang.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenTinhTrang")
        cmbPhong.DataBindings.Clear()
        cmbPhong.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "Tenphong")
        CmbNgan.DataBindings.Clear()
        CmbNgan.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenNgan")
        CmbKe.DataBindings.Clear()
        CmbKe.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenKe")
        CmbKho.DataBindings.Clear()
        CmbKho.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenKho")
        TextMaBienDong.DataBindings.Clear()
        TextMaBienDong.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "MaBienDong")
        cbLoaiBienDong.DataBindings.Clear()
        cbLoaiBienDong.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenLoaiBD")
        CmbXa.DataBindings.Clear()
        CmbXa.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "tenxa")
        CmbHuyen.DataBindings.Clear()
        CmbHuyen.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "T_huyen")
        NapDataGridViewThuaDat()

        NapDataGridViewNguoiMuon()
        NapvaoTexBoxNguoiMuon()
    End Sub
    Private Sub NapDataGridViewThuaDat()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select  thua.sohoso,  thua.tobando,thua.thuadat, thua.dientich, " &
                                " thua.loaidat,tl.tenloaitailieu, thua.thuaid " &
                                 " From  THUADATBIENDONG thua,  DMTailieu tl  " &
                                " WHERE thua.sohoso = '" & TextSoHoSo.Text & "' " &
                                 " and thua.loaitailieu = tl.maloaitailieuID ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewThuaDat.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub Lamtuoi()
        TextSoHoSo.Text = ""
        TextTenChu.Text = ""
        TextDiaChi.Text = ""
        TextSoThua.Text = ""
        TextToBD.Text = ""
        TextDienTich.Text = ""
        CmbLD.Text = ""
        TextMaBienDong.Text = ""
        CmbLoaiTaiLieu.Text = ""
        cmbPhong.Text = ""
        CmbNgan.Text = ""
        CmbKe.Text = ""
        CmbKho.Text = ""
        TextthuaID.Text = ""
        TextNguoiYeuCau.Text = ""
        TextDonVi.Text = ""
        TextMucDich.Text = ""
        '  DataGridViewNguoiMuon.Rows.Clear()
    End Sub
    Private Sub NapVaoTexBoxThuaDat()
        TextthuaID.DataBindings.Clear()
        TextthuaID.DataBindings.Add("text", DataGridViewThuaDat.DataSource, "thuaid")
        TextSoThua.DataBindings.Clear()
        TextSoThua.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "thuadat")
        TextToBD.DataBindings.Clear()
        TextToBD.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "tobando")
        TextDienTich.DataBindings.Clear()
        TextDienTich.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Dientich")
        CmbLD.DataBindings.Clear()
        CmbLD.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Loaidat")
        CmbLoaiTaiLieu.DataBindings.Clear()
        CmbLoaiTaiLieu.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "TenLoaitailieu")
    End Sub

    Private Sub FrmTraHoSoBienDong_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        NapHinhThuc()
        NapLoaiBienDong()
        NapLoaiTaiLieu()
        NapDonViHuyen()
        NapLoaiDat1()
        NapPhong()
        NapNgan()
        NapKe()
        NapKho()
        NapTinhTrang()
        DataGridViewTenChu.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewTenChu.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewThuaDat.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewThuaDat.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewNguoiMuon.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewNguoiMuon.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
    End Sub
    Private Sub NapDataGridViewNguoiMuon()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select cc.SoHoSo,  cc.NguoiMuon, cc.DonVi,  cc.MucDichMuon, cc.ngayMuon,th.TenHinhThuc " &
                                 " From  CCTT_HOSOBIENDONG cc, DMhinhthuc th  " &
                                " WHERE cc.sohoso = '" & TextSoHoSo.Text & "' " &
                                " and cc.hinhthucmuonid = th.mahinhthucid " &
                                " and tinhtrangid = '" & 2 & "' ", ob_cnn)

        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewNguoiMuon.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapvaoTexBoxNguoiMuon()
        TextNguoiYeuCau.DataBindings.Clear()
        TextNguoiYeuCau.DataBindings.Add("text", DataGridViewNguoiMuon.DataSource, "NguoiMuon")
        TextDonVi.DataBindings.Clear()
        TextDonVi.DataBindings.Add("text", DataGridViewNguoiMuon.DataSource, "DonVi")
        DateNgayMuon.DataBindings.Clear()
        DateNgayMuon.DataBindings.Add("text", DataGridViewNguoiMuon.DataSource, "ngaymuon")
        TextMucDich.DataBindings.Clear()
        TextMucDich.DataBindings.Add("text", DataGridViewNguoiMuon.DataSource, "Mucdichmuon")

    End Sub
    Private Sub DataGridViewTenChu_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewTenChu.CellClick
        NapVaoTexBox()
    End Sub

    Private Sub DataGridViewThuaDat_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewThuaDat.CellClick
        NapVaoTexBoxThuaDat()
    End Sub

    Private Sub NapDataGridView()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select  ten.SohoSo, ten.TenChu, ten.DiaChi, ten.mabiendong, lbd.TenLoaiBD, " &
                                " tt.tentinhtrang, p.tenphong, ng.tenngan, k.tenke, kh.tenkho,  tx.tenxa, th.T_huyen  " &
                                " From TENCHUBIENDONG ten, DMTinhTrang tt, LoaiBD lbd, " &
                                " DMPhong p, DMNgan ng, DMKe k, DMKho kh, tenxa tx, tenhuyen th  " &
                                " where ten.TinhtrangID= tt.matinhtrangid " &
                                " and ten.phongID = p.maphongid " &
                                " and ten.nganid = ng.manganid " &
                                " and ten.keid = k.makeid " &
                                " and ten.khoid = kh.makhoid " &
                                " and ten.xaid = tx.maxa" &
                                " and ten.LoaiBienDongID = lbd.maBD " &
                                " and tinhtrangid =  '" & 2 & "' " &
                                " and ten.huyenid = '" & CmbHuyen.SelectedValue.ToString & "' " &
                                " and ten.xaid = '" & CmbXa.SelectedValue.ToString & "' " &
                                " and ten.huyenID = th.mah ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewTenChu.DataSource = tbl
        '
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("Không có thông tin")
        End If
        ob_cnn.Close()
    End Sub
    Private Sub ButtonTimKiem_Click(sender As Object, e As EventArgs) Handles ButtonTimKiem.Click
        Lamtuoi()
        NapDataGridView()
        ButtonLuu.Enabled = True
    End Sub
    Private Sub Update_Tinhtrang()
        Try
            Dim Sql As String = ""
            Sql = " Update TENCHUBIENDONG Set " &
                " TinhTrangid = '" & 1 & "'" &
                " where SoHoSo = '" & TextSoHoSo.Text & "' "
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            '   MsgBox("Sửa tên chủ thành công!")
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
    Private Sub Update_ThongtinNguoiMuon()
        Try
            Dim Ngaytra As String
            Ngaytra = String.Format("{0:MM/dd/yyyy}", DateNgayTra.Value)
            Dim Sql As String = ""
            Sql = " Update CCTT_HOSOBIENDONG Set tinhtrangid = @tinhtrang, ngayTra = @ngaytra WHERE SoHoSo = @sohoso"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.Parameters.AddWithValue("@tinhtrang", 1)
            cmd.Parameters.AddWithValue("@ngaytra", Ngaytra)
            cmd.Parameters.AddWithValue("@sohoso", TextSoHoSo.Text)
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            '   MsgBox("Sửa tên chủ thành công!")
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
    Private Sub LuuNguoiNhapTra()
        Try
            Dim tennhap As String
            tennhap = ModuleTenDangNhap.m_tennhap
            Dim Sql As String = ""
            Sql = " insert into QL_TRAHOSOBIENDONG (SoHoSo, NguoiNhaptra, ngaytra) values (@sohoso, @nguoinhaptra, @ngaytra)"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.Parameters.AddWithValue("@sohoso", TextSoHoSo.Text)
            cmd.Parameters.AddWithValue("@nguoinhaptra", tennhap)
            cmd.Parameters.AddWithValue("@ngaytra", DateTime.Now)
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub ButtonLuu_Click(sender As Object, e As EventArgs) Handles ButtonLuu.Click
        If TextNguoiYeuCau.Text <> "" And TextDonVi.Text <> "" And TextDiaChi.Text <> "" And TextTenChu.Text <> "" Then
            Update_Tinhtrang()
            Update_ThongtinNguoiMuon()
            LuuNguoiNhapTra()
            Lamtuoi()
            ButtonLuu.Enabled = False
            MessageBox.Show("Lưu thông tin thành công ")
            NapDataGridView()
        Else
            MessageBox.Show(" Chưa có thông tin người yêu cầu ! ")
        End If
    End Sub

    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub

    Private Sub DataGridViewNguoiMuon_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewNguoiMuon.CellClick
        NapvaoTexBoxNguoiMuon()
    End Sub

    Private Sub DataGridViewTenChu_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewTenChu.CellContentClick

    End Sub

    Private Sub DataGridViewThuaDat_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewThuaDat.CellContentClick

    End Sub
End Class